package dao;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import entity.User;
import utils.JDBCUtils;
public class UserDao {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    // 添加用户的操作
    public boolean insert(User user) {
        try {
            // 1.获得数据的连接
            conn = JDBCUtils.getConnection();//注册驱动程序，定义jdbcurl,获取链接对象
            //定义sql
            String sql = "INSERT INTO user(name,password,email,birthday) VALUES(?,?,?,?)" ;
            // 2.获得PreparedStatement对象
            pstmt = conn.prepareStatement(sql);
            // 发送SQL语句
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(user.getBirthday());
            //3.替换占位符
            pstmt.setString(1,user.getName());
            pstmt.setString(2,user.getPassword());
            pstmt.setString(3,user.getEmail());
            pstmt.setDate(4,new Date(user.getBirthday().getTime()));

            int num = pstmt.executeUpdate();
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, pstmt, conn);
        }
        return false;
    }
    // 查询所有的User对象
    public ArrayList<User> findAll() {
        ArrayList<User> list = new ArrayList<User>();
        try {
            // 获得数据的连接
            conn = JDBCUtils.getConnection();
            // 发送SQL语句
            String sql = "SELECT * FROM user";
            // 获得Statement对象
            pstmt = conn.prepareStatement(sql);
           //因为无占位符，无需替换
            rs = pstmt.executeQuery();
            // 处理结果集
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setBirthday(rs.getDate("birthday"));
                list.add(user);
            }
            //将封装好的数据作为返回值
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, pstmt, conn);
        }
        return null;
    }
    // 根据id查找指定的user
    public User findById(int id) {
        User user = null;
        try {
            // 获得数据的连接
            conn = JDBCUtils.getConnection();
            // 发送SQL语句
            String sql = "SELECT * FROM user WHERE id=?" ;
            // 获得preparedStatement对象
            pstmt = conn.prepareStatement(sql);
            //替换占位符
            pstmt.setInt(1,id);
            //执行sql
            rs = pstmt.executeQuery();
            // 处理结果集
            if (rs.next()) {
                user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setBirthday(rs.getDate("birthday"));
            }
            return user;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, pstmt, conn);
        }
        return null;
    }
    
 // 根据id查找指定的user
    public User findByName(String name) {
        User user = null;
        try {
            // 获得数据的连接
            conn = JDBCUtils.getConnection();
            // 发送SQL语句
            String sql = "SELECT * FROM user WHERE name=?" ;
            //String sql = "SELECT * FROM user WHERE name='"+name+"'";//name="abc or 1=1 or name='1"
            // 获得Statement对象
            pstmt = conn.prepareStatement(sql);
            // 替换占位符
            pstmt.setString(1,name);
            //执行sql
            rs = pstmt.executeQuery();
            // 处理结果集
            if (rs.next()) {
                user = new User();
                user.setId(rs.getInt("id"));
                user.setName(name);
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setBirthday(rs.getDate("birthday"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, pstmt, conn);
        }
        return user;
    }
    
    // 删除用户
    public boolean delete(int id) {
        try {
            // 获得数据的连接
            conn = JDBCUtils.getConnection();
            // 发送SQL语句
            String sql = "DELETE FROM user WHERE id=?";
            // 获得Statement对象
            pstmt = conn.prepareStatement(sql);
            //替换占位符
            pstmt.setInt(1,id);
            //执行sql
            int num = pstmt.executeUpdate();
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, pstmt, conn);
        }
        return false;
    }
    // 修改用户
    public boolean update(User user) {
        try {
            // 获得数据的连接
            conn = JDBCUtils.getConnection();
            // 发送SQL语句
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(user.getBirthday());
            String sql = "UPDATE user set name=?"
                    + ",password=?"
                    +",email=?"
                    + ",birthday=?"
                    + " WHERE id=?";
            /*String sql = "UPDATE user set name='" + user.getName()
                    + "',password='" + user.getPassword() + "',email='"
                    + user.getEmail() + "',birthday='" + birthday
                    + "' WHERE id=" + user.getId();*/
            // 获得Statement对象
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,user.getName());
            pstmt.setString(2,user.getPassword());
            pstmt.setString(3,user.getEmail());
            pstmt.setDate(4,new Date(user.getBirthday().getTime()));
            pstmt.setInt(5,user.getId());
            int num = pstmt.executeUpdate();
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, pstmt, conn);
        }
        return false;
    }
}
